/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 *
 * Copyright 2009 - 2012 Luca Mingardi.
 *
 * This file is part of jeeObserver.
 *
 * JeeObserver is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * JeeObserver is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 */
package jeeobserver.utilities;

import java.nio.ByteBuffer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import jeeobserver.server.RequestParameter;

/**
 * The Class TimeUtilities.
 *
 * @author Luca Mingardi
 * @version 4.0
 */
public final class DatabaseUtilities {

	public static String getLinearRegressionQuery(String count, String x, String y, String name) {


		//Regression Equation(y) = mx + b
		//Slope(m) = (NΣXY - (ΣX)(ΣY)) / (NΣX^2 - (ΣX)^2)
		//Intercept(b) = (ΣY - b(ΣX)) / N


		String querySlope = "((({count} * SUM({x} * {y})) - (SUM({x}) * SUM({y}))) / (({count} * SUM({x} * {x}))-(SUM({x}) * SUM({x}))))";
		String queryIntercept = "((SUM({y}) - (" + querySlope + " * SUM({x}))) / {count})";
		String query = querySlope + " AS {name}_m, " + queryIntercept + " AS {name}_b ";


		/* Originale
		 * String query =
		 "((({count} * SUM({x} * {y})) - (SUM({x}) * SUM({y})))/(({count} * SUM({x} * {x}))-(SUM({x}) * SUM({x})))) AS {name}_m, "
		 //+"0 AS {name}_b ";
		 + "(AVG({y}) - (({count} * SUM({x} * {y})) - (SUM({x}) * SUM({y}))) / ((({count} * SUM({x} * {x})))-(SUM({x}) * SUM({x}))) * AVG({x})) as {name}_b ";
		 */

		query = query.replace("{name}", name);
		query = query.replace("{count}", count);
		query = query.replace("{x}", x);
		query = query.replace("{y}", y);

		return query;

	}

	public static Date calculateStatisticTimestamp(Date timestamp, int samplingPeriod) {
		Calendar calendar = new GregorianCalendar();
		calendar.setTime(timestamp);

		if (samplingPeriod == RequestParameter.SAMPLING_PERIOD_SECOND) {
			calendar.set(Calendar.MILLISECOND, 0);
		} else if (samplingPeriod == RequestParameter.SAMPLING_PERIOD_MINUTE) {
			calendar.set(Calendar.MILLISECOND, 0);
			calendar.set(Calendar.SECOND, 0);
		} else if (samplingPeriod == RequestParameter.SAMPLING_PERIOD_HOUR) {
			calendar.set(Calendar.MILLISECOND, 0);
			calendar.set(Calendar.SECOND, 0);
			calendar.set(Calendar.MINUTE, 0);
		} else if (samplingPeriod == RequestParameter.SAMPLING_PERIOD_DAY) {
			calendar.set(Calendar.MILLISECOND, 0);
			calendar.set(Calendar.SECOND, 0);
			calendar.set(Calendar.MINUTE, 0);
			calendar.set(Calendar.HOUR_OF_DAY, 0);
		} else if (samplingPeriod == RequestParameter.SAMPLING_PERIOD_WEEK) {
			calendar.set(Calendar.MILLISECOND, 0);
			calendar.set(Calendar.SECOND, 0);
			calendar.set(Calendar.MINUTE, 0);
			calendar.set(Calendar.HOUR_OF_DAY, 0);
			calendar.set(Calendar.DAY_OF_WEEK, 1);
		} else if (samplingPeriod == RequestParameter.SAMPLING_PERIOD_MONTH) {
			calendar.set(Calendar.MILLISECOND, 0);
			calendar.set(Calendar.SECOND, 0);
			calendar.set(Calendar.MINUTE, 0);
			calendar.set(Calendar.HOUR_OF_DAY, 0);
			calendar.set(Calendar.DAY_OF_MONTH, 1);
		} else if (samplingPeriod == RequestParameter.SAMPLING_PERIOD_YEAR) {
			calendar.set(Calendar.MILLISECOND, 0);
			calendar.set(Calendar.SECOND, 0);
			calendar.set(Calendar.MINUTE, 0);
			calendar.set(Calendar.HOUR_OF_DAY, 0);
			calendar.set(Calendar.DAY_OF_MONTH, 1);
			calendar.set(Calendar.MONTH, 0);
		} else if (samplingPeriod == RequestParameter.SAMPLING_PERIOD_NONE) {
			calendar.setTimeInMillis(0);
		}

		return new Date(calendar.getTime().getTime());
	}

	/**
	 * Gets the resized string.
	 *
	 * @param value the value
	 * @param maxLength the max length
	 *
	 * @return the resized string
	 */
	public static String getResizedString(String value, int maxLength) {
		if ((value != null) && (value.length() > maxLength)) {
			return value.substring(0, maxLength);
		}

		return value;
	}

	public static String getFilledString(String value, int minLength) {
		if ((value != null)) {
			while (value.length() < minLength) {
				value = value + " ";
			}
		}

		return value;
	}

	public static String getString(ByteBuffer buffer, int lenght) {
		byte bytes[] = new byte[lenght];

		for (int i = 0; i < lenght; i++) {
			bytes[i] = buffer.get();
		}

		return new String(bytes);
	}

	public static String getSchemaAndTableName(String schemaName, String tableName) {
		if (schemaName != null) {
			return schemaName + "." + tableName;
		}
		return tableName;
	}

	public static String removeOrphanWhereAndAndFromSelect(String queryString) {

		String result = queryString;

		if (result != null) {
			if (result.endsWith(" WHERE ")) {
				result = result.substring(0, result.lastIndexOf(" WHERE "));
			} else if (result.endsWith(" WHERE")) {
				result = result.substring(0, result.lastIndexOf(" WHERE"));
			} else if (result.endsWith(" HAVING ")) {
				result = result.substring(0, result.lastIndexOf(" HAVING "));
			} else if (result.endsWith(" HAVING")) {
				result = result.substring(0, result.lastIndexOf(" HAVING"));
			} else if (result.endsWith(" AND ")) {
				result = result.substring(0, result.lastIndexOf(" AND "));
			} else if (result.endsWith(" AND")) {
				result = result.substring(0, result.lastIndexOf(" AND"));
			}

			return result.trim();
		}

		return null;
	}

	public static PreparedStatement createElementTable(Connection connection, String schema, String table, String columns) throws SQLException {
		//return new DebugPreparedStatement(connection, );
		return null;
	}

	public static PreparedStatement selectElementIdsStatement(Connection connection, String schema, String table) throws SQLException {
		//return new DebugPreparedStatement(connection, );
		return null;
	}

	public static PreparedStatement createSelectStatement(Connection connection, String schema, String table, String[] columns, String[][] conditions) throws SQLException {


		//Replace...
		//[schema]
		//[table]
		//[conditions]
		//[selectColumns]
		//[updateColumns]
		//[insertColumns]



		String query = "SELECT " + columns + " ";

		//Insert columns
		for (int i = 0; i < columns.length; i++) {
			if (i > 0) {
				query = query + ", ";
			}
			query = query + columns[i];
		}

		query = query + " ";

		if (schema != null) {
			query = query + schema + ".";
		}

		query = query + table + " WHERE ";

		//Insert conditions
		for (int i = 0; i < conditions.length; i++) {
			if (i > 0) {
				query = query + ", ";
			}
			query = query + conditions[i][0] + " " + conditions[i][1] + "?";
		}

		return connection.prepareStatement(query);
	}

	public static ResultSet executeQuery(PreparedStatement statement, Object[] conditions) throws SQLException {

		int index = 0;
		for (int i = 0; i < conditions.length; i++) {
			if (conditions[i] != null) {
				statement.setObject(index, conditions[i]);
				index = index + 1;
			}
		}

		return statement.executeQuery();
	}
}
